package com.sjsu.cmpe239.database;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.TreeMap;

import com.sjsu.cmpe239.Constants;
import com.sjsu.cmpe239.beans.Movie;
import com.sjsu.cmpe239.connection.ConnectionPool;

import net.sf.json.JSONObject;
//import connection.ConnectionPool;

public class DatabaseManager {

	

	public static void main(String args[])
	{
		
		
		
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet = null;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();
		
		int []usrPref = {0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0};
		float [][]corelationMatrix = new float[19][19];
		HashMap<Integer, Object[]> hmMovieCorelation = new HashMap<Integer, Object[]>();
		
		try {
			String strStmt = "select * from movies ";
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(strStmt);
			int movieCount = 0;
			while(resultSet.next()) {
				Map<String, Object> mapRow = new HashMap<String, Object>();
				Object[] arrString = null;

				arrString = new Object [24];

				arrString[0] = "";
				arrString[1] = resultSet.getInt(5);
				arrString[2] = resultSet.getInt(6);
				arrString[3] = resultSet.getInt(7);
				arrString[4] = resultSet.getInt(8);
				arrString[5] = resultSet.getInt(9);
				arrString[6] = resultSet.getInt(10);
				arrString[7] = resultSet.getInt(11);
				arrString[8] = resultSet.getInt(12);
				arrString[9] = resultSet.getInt(13);
				arrString[10] = resultSet.getInt(14);
				arrString[11] = resultSet.getInt(15);
				arrString[12] = resultSet.getInt(16);
				arrString[13] = resultSet.getInt(17);
				arrString[14] = resultSet.getInt(18);
				arrString[15] = resultSet.getInt(19);
				arrString[16] = resultSet.getInt(20);
				arrString[17] = resultSet.getInt(21);
				arrString[18] = resultSet.getInt(22);
				arrString[19] = resultSet.getInt(23);
				arrString[20] = resultSet.getInt(1);
				arrString[21] = resultSet.getString(2);
				arrString[22] = resultSet.getDate(3);
				arrString[23] = 0.0;
				
				for(int i = 2; i < 20; i++) {
					for(int j = i + 1; j < 20; j++) {
						if((Integer)arrString[i] == 1 && (Integer)arrString[j] == 1) {
							corelationMatrix[i - 1][i - 1]++;
							corelationMatrix[i - 1][j - 1]++;
							corelationMatrix[j - 1][i - 1]++;
						}
					}
					if((Integer)arrString[i] == 1) {
						arrString[0] = arrString[0] + "  " + Constants.genre[i - 2] ;
						arrString[23] = ((Double)arrString[23]) + 1;
					}
				}
				movieCount++;
				hmMovieCorelation.put((Integer)arrString[20], arrString);
			}
			
			for(int i = 1; i < 19; i++) {
				float divisior = corelationMatrix[i][i];
				for(int j = 1; j < 19; j++) {
					if(corelationMatrix[i][i] != 0 )
						corelationMatrix[i][j] /= divisior;
				}
			}
			int cnt = 0;
			Double[] corelationScore = new Double[movieCount];
			
			String strCount = "select * from movierating";
			ResultSet resultSetRating = stmt.executeQuery(strCount);
			List<Float> alList = new ArrayList<Float>();
			movieCount = 0;
			while(resultSetRating.next()) {
				alList.add(resultSetRating.getFloat(2));
				corelationScore[movieCount] = 0.0;
				movieCount++;
			}
			
			int userGenre = 0;
			for(int i = 0; i < 18; i++) {
				if(usrPref[i] == 1) {
					Iterator iterator = hmMovieCorelation.keySet().iterator();
					while(iterator.hasNext()) {
						double score = 0.0;
						Object[] movie = hmMovieCorelation.get(iterator.next());
						cnt++;

						for(int j = 2; j < 20; j++) {
							if((Integer)movie[j] == 1 && i == (j - 2)) {
								score += corelationMatrix[i + 1][j - 1];
							}
						}
						corelationScore[(Integer)movie[20] - 1] += score ;
					}
					userGenre++;
				}
			}
			TreeMap<Double, Integer> tmScore =new TreeMap<Double, Integer>();
			for(int i = 0; i < movieCount; i++) {
				tmScore.put(((corelationScore[i] * alList.get(i))), i + 1);
			}
			List <Object[]> lTopMovies = new ArrayList<Object[]>(); 
			for(int i = 0; i < 10; i++) {
				//System.out.println(tmScore.lastEntry());
				Double score = tmScore.lastEntry().getKey();
				int id = tmScore.get(score);
				tmScore.remove(score);
				Object[] movie = hmMovieCorelation.get(id);
				System.out.println("no. " + (i + 1) + "  Movie " + movie[21]  + "  Genre " + " " + movie[0]);
				lTopMovies.add(movie);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
		try {
		} catch(Exception e) {
		}
	}
	
	
	public List<Movie> getContentbasedRecomendation(int[] usrPref) {
		
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet = null;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();
		
		float [][]corelationMatrix = new float[19][19];
		HashMap<Integer, Object[]> hmMovieCorelation = new HashMap<Integer, Object[]>();
		List <Movie> lTopMovies = new ArrayList<Movie>(); 

		
		try {
			String strStmt = "select * from movies ";
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(strStmt);
			int movieCount = 0;
			while(resultSet.next()) {
				Map<String, Object> mapRow = new HashMap<String, Object>();
				Object[] arrString = null;

				arrString = new Object [24];

				arrString[0] = "";
				arrString[1] = resultSet.getInt(5);
				arrString[2] = resultSet.getInt(6);
				arrString[3] = resultSet.getInt(7);
				arrString[4] = resultSet.getInt(8);
				arrString[5] = resultSet.getInt(9);
				arrString[6] = resultSet.getInt(10);
				arrString[7] = resultSet.getInt(11);
				arrString[8] = resultSet.getInt(12);
				arrString[9] = resultSet.getInt(13);
				arrString[10] = resultSet.getInt(14);
				arrString[11] = resultSet.getInt(15);
				arrString[12] = resultSet.getInt(16);
				arrString[13] = resultSet.getInt(17);
				arrString[14] = resultSet.getInt(18);
				arrString[15] = resultSet.getInt(19);
				arrString[16] = resultSet.getInt(20);
				arrString[17] = resultSet.getInt(21);
				arrString[18] = resultSet.getInt(22);
				arrString[19] = resultSet.getInt(23);
				arrString[20] = resultSet.getInt(1);
				arrString[21] = resultSet.getString(2);
				arrString[22] = resultSet.getDate(3);
				arrString[23] = 0;
				
				for(int i = 2; i < 20; i++) {
					for(int j = i + 1; j < 20; j++) {
						if((Integer)arrString[i] == 1 && (Integer)arrString[j] == 1) {
							corelationMatrix[i - 1][i - 1]++;
							corelationMatrix[i - 1][j - 1]++;
							corelationMatrix[j - 1][i - 1]++;
						}
					}
					if((Integer)arrString[i] == 1) {
						arrString[0] = arrString[0] + "  " + Constants.genre[i - 2] ;
						arrString[23] = ((Integer)arrString[23]) + 1;
					}
				}
				movieCount++;
				hmMovieCorelation.put((Integer)arrString[20], arrString);
			}
			
			for(int i = 1; i < 19; i++) {
				float divisior = corelationMatrix[i][i];
				for(int j = 1; j < 19; j++) {
					if(corelationMatrix[i][i] != 0 )
						corelationMatrix[i][j] /= divisior;
				}
			}
			int cnt = 0;
			Double[] corelationScore = new Double[movieCount];
			
			String strCount = "select * from movierating";
			ResultSet resultSetRating = stmt.executeQuery(strCount);
			List<Float> alList = new ArrayList<Float>();
			movieCount = 0;
			while(resultSetRating.next()) {
				alList.add(resultSetRating.getFloat(2));
				corelationScore[movieCount] = 0.0;
				movieCount++;
			}
			
			int userGenre = 0;
			for(int i = 0; i < 18; i++) {
				if(usrPref[i] == 1) {
					Iterator iterator = hmMovieCorelation.keySet().iterator();
					while(iterator.hasNext()) {
						double score = 0.0;
						Object[] movie = hmMovieCorelation.get(iterator.next());
						cnt++;

						for(int j = 2; j < 20; j++) {
							if((Integer)movie[j] == 1) {
								score += corelationMatrix[i + 1][j - 1];
							}
						}
						corelationScore[(Integer)movie[20] - 1] += score ;
					}
					userGenre++;
				}
			}
			TreeMap<Double, Integer> tmScore =new TreeMap<Double, Integer>();
			for(int i = 0; i < movieCount; i++) {
				tmScore.put(((corelationScore[i] * alList.get(i))), i + 1);
			}
			for(int i = 0; i < 10; i++) {
				//System.out.println(tmScore.lastEntry());
				Double score = tmScore.lastEntry().getKey();
				int id = tmScore.get(score);
				tmScore.remove(score);
				Object[] movie = hmMovieCorelation.get(id);
				System.out.println("no " + i + "Movie " + movie[21]  + "Genre " + " " + movie[0]);
				Movie movieBean = new Movie();
				movieBean.setMovieName(movie[21].toString());
				movieBean.setMovieId(movie[20].toString());
				movieBean.setDate(movie[22].toString());
				movieBean.setGenres(movie[0].toString());
				lTopMovies.add(movieBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
		try {
		} catch(Exception e) {
		}
		
		return lTopMovies;
	}
	
	public String listMovies(String page, String sortName, String sortOrder, String qtype, String query, String rp){
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();

		try {
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			int ipage = Integer.parseInt(page); 
			int irp = Integer.parseInt("10"); 

			// Setup sort and search SQL using posted data
			String sortSql = "order by " + sortName + " " + sortOrder;
			String searchSql = (!qtype.trim().equals("") && !query.trim().equals("")) ? "where " + qtype +  "= " + "'" + query + "'" : "";
			// Get total count of records

			String strCount = "select count(*) as COUNT from movies " + searchSql;
			resultSet = stmt.executeQuery(strCount);
			resultSet.next();
			int total = resultSet.getInt("COUNT");
			int pageStart = (ipage - 1) * (irp);
			String limitSql = " limit " +  pageStart + ", " +  rp;

			String strQuery = "select * from movies " 
				+ searchSql + sortSql + limitSql;
			resultSet = stmt.executeQuery(strQuery);



			List<Map> ljsonRow = new ArrayList<Map>();
			List<Map> ljsonCacheRow = new ArrayList<Map>();
			int i = 0;
			int icache = ipage;
			Map<String, Object> mapCache = new HashMap<String, Object>();
			while(resultSet.next()) {
				i++;
				Map<String, Object> mapRow = new HashMap<String, Object>();
				String[] arrString = null;
				
				arrString = new String [4];
				

				mapRow.put("id", i + "");
				try {
					arrString[0] = resultSet.getInt(1)+ "";
					arrString[2] = resultSet.getString(2) + "";
					arrString[3] = resultSet.getDate(3) + "";
				} catch (Exception e) {
					
				}
				arrString[0] = arrString[0] == null ? "" : arrString[0];
				arrString[1] = "<img id =' " + arrString[0] + " ' height = '200px' onclick = 'displayMovie(this)' width = '100px' src='images/ananomous.jpg'/>";
				arrString[2] = arrString[2] == null ? "" : arrString[2];
				arrString[3] = arrString[3] == null ? "" : arrString[3];

				
				if(resultSet.getInt(6) == 1) {
					arrString[3] += Constants.genre[0] + " ";
				} 
				if(resultSet.getInt(7) == 1) {
					arrString[3] += Constants.genre[1] + " ";
				}
				if(resultSet.getInt(8) == 1) {
					arrString[3] += Constants.genre[2] + " ";
				}
				if(resultSet.getInt(9) == 1) {
					arrString[3] += Constants.genre[3] + " ";
				}
				if(resultSet.getInt(10) == 1) {
					arrString[3] += Constants.genre[4] + " ";
				}
				if(resultSet.getInt(11) == 1) {
					arrString[3] += Constants.genre[5] + " ";
				}
				if(resultSet.getInt(12) == 1) {
					arrString[3] += Constants.genre[6] + " ";
				}
				if(resultSet.getInt(13) == 1) {
					arrString[3] += Constants.genre[7] + " ";
				}
				if(resultSet.getInt(14) == 1) {
					arrString[3] += Constants.genre[8] + " ";
				}
				if(resultSet.getInt(15) == 1) {
					arrString[3] += Constants.genre[9] + " ";
				}
				if(resultSet.getInt(16) == 1) {
					arrString[3] += Constants.genre[10] + " ";
				}
				if(resultSet.getInt(17) == 1) {
					arrString[3] += Constants.genre[11] + " ";
				}
				if(resultSet.getInt(18) == 1) {
					arrString[3] += Constants.genre[12] + " ";
				}
				if(resultSet.getInt(19) == 1) {
					arrString[3] += Constants.genre[13] + " ";
				}
				if(resultSet.getInt(20) == 1) {
					arrString[3] += Constants.genre[14] + " ";
				}
				if(resultSet.getInt(21) == 1) {
					arrString[3] += Constants.genre[15] + " ";
				}
				if(resultSet.getInt(22) == 1) {
					arrString[3] += Constants.genre[16] + " ";
				}
				if(resultSet.getInt(23) == 1) {
					arrString[3] += Constants.genre[17] + " ";
				}

				/*************************************************
				 * This is dynamic generation of html using JSON
				 ************************************************/
				
				mapRow.put("cell", arrString);
				ljsonRow.add(mapRow);
			}

			mjsonResult.put("page", page + "");
			mjsonResult.put("total", total + "");
			mjsonResult.put("rows", ljsonRow);
		} catch (SQLException e) {
			return "Unable to load Movie list";
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
					return "Unable to load Movie list";
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
		JSONObject jsonObj = null;
		try {
			jsonObj = JSONObject.fromObject(mjsonResult);
		} catch(Exception e) {
			return "Unable to load Movie list";
		}
		return jsonObj.toString();
	}

	public void saveRating(int id, int movieId, int rating){
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();

		try {
			connection = conPoolObj.getConnection();
			String strQuery = "INSERT INTO ratings VALUES( ?, ?, ?, ?)";
			PreparedStatement pStmnt = connection.prepareStatement(strQuery);
			pStmnt.setInt(1, id);
			pStmnt.setInt(2, movieId);
			pStmnt.setInt(3, rating);
			pStmnt.setDate(4, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
			pStmnt.executeUpdate();
			
			String strCount = "select count(*) as SUM from ratings where MoviesId =" + movieId;
			stmt = connection.createStatement();
			ResultSet resultSetRating = stmt.executeQuery(strCount);
			resultSetRating.next();
			int total = resultSetRating.getInt("SUM");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
					sqe.printStackTrace();
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
	}

	public int authenticateUser(String userName, String password){
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();

		try {
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			 
			// Setup sort and search SQL using posted data
			String strCount = "select count(*) as SUM from users where id = " + userName;
			stmt = connection.createStatement();
			ResultSet resultSetRating = stmt.executeQuery(strCount);
			resultSetRating.next();
			int total = resultSetRating.getInt("SUM");
			if(total == 0)
				return 0;
			else {
				strCount = "select count(*) as SUM from ratings where Id =" + userName;
				stmt = connection.createStatement();
				resultSetRating = stmt.executeQuery(strCount);
				resultSetRating.next();
				total = resultSetRating.getInt("SUM");
				if(total < 10)
					return 1;
				else
					return 2;
			}
			
		} catch (SQLException e) {
			return 0;
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
					return 0;
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
	}
	
	public String getMovie(int id) {
		int movieCount = 0;
		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();
		Movie movieObj = new Movie();

		try {
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			 
			// Setup sort and search SQL using posted data
			String strCount = "select * from movies where id = " + id;
			stmt = connection.createStatement();
			resultSet = stmt.executeQuery(strCount);
			List<Map> ljsonRow = new ArrayList<Map>();
			List<Map> ljsonCacheRow = new ArrayList<Map>();
			Map<String, Object> mapRow = new HashMap<String, Object>();
			resultSet.next();
			String genres = "";
			movieObj.setMovieId(resultSet.getInt(1) + "");
			movieObj.setMovieName(resultSet.getString(2) + "");
			movieObj.setDate(resultSet.getString(3) + "");

			if (resultSet.getInt(6) == 1) {
				genres += Constants.genre[0] + " ";
			}
			if (resultSet.getInt(7) == 1) {
				genres += Constants.genre[1] + " ";
			}
			if (resultSet.getInt(8) == 1) {
				genres += Constants.genre[2] + " ";
			}
			if (resultSet.getInt(9) == 1) {
				genres += Constants.genre[3] + " ";
			}
			if (resultSet.getInt(10) == 1) {
				genres += Constants.genre[4] + " ";
			}
			if (resultSet.getInt(11) == 1) {
				genres += Constants.genre[5] + " ";
			}
			if (resultSet.getInt(12) == 1) {
				genres += Constants.genre[6] + " ";
			}
			if (resultSet.getInt(13) == 1) {
				genres += Constants.genre[7] + " ";
			}
			if (resultSet.getInt(14) == 1) {
				genres += Constants.genre[8] + " ";
			}
			if (resultSet.getInt(15) == 1) {
				genres += Constants.genre[9] + " ";
			}
			if (resultSet.getInt(16) == 1) {
				genres += Constants.genre[10] + " ";
			}
			if (resultSet.getInt(17) == 1) {
				genres += Constants.genre[11] + " ";
			}
			if (resultSet.getInt(18) == 1) {
				genres += Constants.genre[12] + " ";
			}
			if (resultSet.getInt(19) == 1) {
				genres += Constants.genre[13] + " ";
			}
			if (resultSet.getInt(20) == 1) {
				genres += Constants.genre[14] + " ";
			}
			if (resultSet.getInt(21) == 1) {
				genres += Constants.genre[15] + " ";
			}
			if (resultSet.getInt(22) == 1) {
				genres += Constants.genre[16] + " ";
			}
			if (resultSet.getInt(23) == 1) {
				genres += Constants.genre[17] + " ";
			}
			
			movieObj.setGenres(genres);
			String[] arrString = new String[2];
			arrString[0] = "<img height = '200px' width = '100px' src='images/ananomous.jpg'/><br>Genre&nbsp; &nbsp; &nbsp;<font color = 'red' >" + movieObj.getGenres() + "</font><br>Release Date&nbsp; &nbsp; &nbsp;" + movieObj.getDate();
			arrString[1] = "<font  >" + movieObj.getMovieName() + "</font>";
			mapRow.put("cell", arrString);
			ljsonRow.add(mapRow);
	
			mjsonResult.put("page", "1");
			mjsonResult.put("total", "1");
			mjsonResult.put("rows", ljsonRow);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
					sqe.printStackTrace();
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
		JSONObject jsonObj = null;
		try {
			jsonObj = JSONObject.fromObject(mjsonResult);
		} catch(Exception e) {
			e.printStackTrace();
		}
		return jsonObj.toString();
	}


	public int registerUser() {

		Connection connection = null;
		Statement stmt = null;
		ConnectionPool conPoolObj = ConnectionPool.getConnectionPoolInstance();
		HashMap<String, Object> hmKeyValueMap = new HashMap<String, Object>();
		ResultSet resultSet;
		Map<String, Object> mjsonResult = new HashMap<String, Object>();

		try {
			connection = conPoolObj.getConnection();
			stmt = connection.createStatement();
			 
			// Setup sort and search SQL using posted data
			String strCount = "select count(*) as SUM from users ";
			stmt = connection.createStatement();
			ResultSet resultSetRating = stmt.executeQuery(strCount);
			resultSetRating.next();
			int total = resultSetRating.getInt("SUM");
			String strQuery = "INSERT INTO users VALUES( ?, ?, ?, ?, ?)";
			PreparedStatement pStmnt = connection.prepareStatement(strQuery);
			pStmnt.setInt(1, total + 1);
			pStmnt.setInt(2, 22);
			pStmnt.setString(3, "M");
			pStmnt.setString(4, "musician");
			pStmnt.setString(5, "00000");
			pStmnt.executeUpdate();
			
		} catch (SQLException e) {
			return 0;
		}	finally{
			if(stmt!=null){
				try{
					stmt.close();
				}catch(SQLException sqe){
					return 0;
				}
			}
			if(connection!=null){
				conPoolObj.free(connection);
			}
		}
		return 1;
	
	}

}
